package com.situ.edu.dao.impl;

import com.situ.edu.dao.IStudentHisDao;
import com.situ.edu.pojo.query.CourseQuery;
import com.situ.edu.pojo.vo.CourseVO;
import com.situ.edu.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.ObjectUtils;

import java.util.ArrayList;
import java.util.List;

public class StudentHisDaoImpl implements IStudentHisDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public List<CourseVO> selectByPage(CourseQuery courseQuery, String name, int password) {
        String sql = "SELECT c.id,c.`name` ,c.gredit,e.scores,x.`name` AS xiName,c.create_time,c.update_time\n" +
                "FROM `user` AS u INNER JOIN student AS s\n" +
                "ON u.id=s.user_id INNER JOIN elective AS e\n" +
                "ON s.id=e.student_id INNER JOIN course AS c\n" +
                "ON c.id=e.course_id INNER JOIN xi AS x\n" +
                "ON x.id=s.xi_id where u.name=? AND u.password=? AND 1=1";

        List<Object> list = new ArrayList<>();

        list.add(name);
        list.add(password);

        //搜索课程id
        if (!ObjectUtils.isEmpty(courseQuery.getId())){
            sql += " and c.id=?";
            list.add(courseQuery.getId());
        }
        sql += " limit ?,?";
        int offset = (courseQuery.getPage() - 1)*courseQuery.getLimit();
        list.add(offset);
        list.add(courseQuery.getLimit());
        return template.query(sql,
                new BeanPropertyRowMapper<>(CourseVO.class),list.toArray());
    }

    @Override
    public int selectTotalCount(CourseQuery courseQuery, String name, int password) {

        String sql = "SELECT count(*)\n" +
                "FROM `user` AS u INNER JOIN student AS s\n" +
                "ON u.id=s.user_id INNER JOIN elective AS e\n" +
                "ON s.id=e.student_id INNER JOIN course AS c\n" +
                "ON c.id=e.course_id INNER JOIN xi AS x\n" +
                "ON x.id=s.xi_id where u.name=? AND u.password=? AND 1=1";
        List<Object> list = new ArrayList<>();
        list.add(name);
        list.add(password);

        //搜索课程id
        if (!ObjectUtils.isEmpty(courseQuery.getId())){
            sql += " and c.id=?";
            list.add(courseQuery.getId());
        }

        return template.queryForObject(sql,Integer.class,list.toArray());

    }
    /*@Override
    public List<CourseVO> selectByPage(CourseQuery courseQuery) {
        *//*String sql = "SELECT s.id,s.`name` AS studentName,c.id,c.`name`,c.gredit,e.scores,x.`name` AS xiName,c.create_time,c.update_time\n" +
                "FROM `user` AS u INNER JOIN student AS s\n" +
                "ON u.id=s.user_id INNER JOIN elective AS e\n" +
                "ON s.id=e.student_id INNER JOIN course AS c\n" +
                "ON c.id=e.course_id INNER JOIN xi AS x\n" +
                "ON x.id=s.xi_id where 1=1";*//*
        String sql = "SELECT s.id,s.`name` AS studentName,c.`name` AS courseName,c.gredit,e.scores,x.`name` AS xiName,c.create_time,c.update_time\n" +
                "FROM `user` AS u INNER JOIN student AS s\n" +
                "ON u.id=s.user_id INNER JOIN elective AS e\n" +
                "ON s.id=e.student_id INNER JOIN course AS c\n" +
                "ON c.id=e.course_id INNER JOIN xi AS x\n" +
                "ON x.id=s.xi_id where u.name=? AND u.password=? AND 1=1";

        List<Object> list = new ArrayList<>();
        //搜索课程id
        if (!ObjectUtils.isEmpty(courseQuery.getId())){
            sql += " and c.id=?";
            list.add(courseQuery.getId());
        }
        sql += " limit ?,?";
        int offset = (courseQuery.getPage() - 1)*courseQuery.getLimit();
        list.add(offset);
        list.add(courseQuery.getLimit());
        return template.query(sql,
                new BeanPropertyRowMapper<>(CourseVO.class),list.toArray());
    }

    @Override
    public int selectTotalCount(CourseQuery courseQuery) {
        String sql = "SELECT count(*)\n" +
                "FROM `user` AS u INNER JOIN student AS s\n" +
                "ON u.id=s.user_id INNER JOIN elective AS e\n" +
                "ON s.id=e.student_id INNER JOIN course AS c\n" +
                "ON c.id=e.course_id INNER JOIN xi AS x\n" +
                "ON x.id=s.xi_id where 1=1";
        List<Object> list = new ArrayList<>();
        //搜索课程id
        if (!ObjectUtils.isEmpty(courseQuery.getId())){
            sql += " and c.id=?";
            list.add(courseQuery.getId());
        }

        return template.queryForObject(sql,Integer.class,list.toArray());
    }*/
}
